package pannel.recipe;

import utils.JDBCUtils;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.time.LocalDateTime;

public class RecipeDao {
    private static final String TABLE_NAME = "recipe";

    // 新增菜谱
    public boolean insert(Recipe recipe) throws SQLException {
        String sql = "INSERT INTO " + TABLE_NAME + 
                    " (name, description, category_id, image_path) VALUES (?, ?, ?, ?)";
        int affected = (int) JDBCUtils.execute(sql,
                recipe.getName(),
                recipe.getDescription(),
                recipe.getCategoryId(),
                recipe.getImagePath()
        );
        return affected > 0;
    }

    // 根据ID更新菜谱
    public boolean updateById(Recipe recipe) throws SQLException {
        String sql = "UPDATE " + TABLE_NAME + 
                    " SET name = ?, description = ?, category_id = ?, image_path = ? WHERE id = ?";
        int affected = (int) JDBCUtils.execute(sql,
                recipe.getName(),
                recipe.getDescription(),
                recipe.getCategoryId(),
                recipe.getImagePath(),
                recipe.getId()
        );
        return affected > 0;
    }

    // 根据ID删除菜谱
    public boolean deleteById(Integer id) throws SQLException {
        String sql = "DELETE FROM " + TABLE_NAME + " WHERE id = ?";
        int affected = (int) JDBCUtils.execute(sql, id);
        return affected > 0;
    }

    // 分页模糊查询（包含分类名称）
    public List<Recipe> pageQuery(String name, Integer categoryId, Integer pageNum, Integer pageSize) 
            throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT r.*, c.name as category_name FROM ")
           .append(TABLE_NAME)
           .append(" r LEFT JOIN category c ON r.category_id = c.id WHERE 1=1");
        
        List<Object> params = new ArrayList<>();
        
        if (name != null && !name.trim().isEmpty()) {
            sql.append(" AND r.name LIKE ?");
            params.add("%" + name + "%");
        }
        
        if (categoryId != null) {
            sql.append(" AND r.category_id = ?");
            params.add(categoryId);
        }
        
        sql.append(" LIMIT ?, ?");
        params.add((pageNum - 1) * pageSize);
        params.add(pageSize);

        ResultSet rs = (ResultSet) JDBCUtils.execute(sql.toString(), params.toArray());

        List<Recipe> recipes = new ArrayList<>();
        while (rs.next()) {
            recipes.add(mapResultSetToRecipe(rs));
        }
        return recipes;
    }

    // 获取总记录数
    public int getTotalCount(String name, Integer categoryId) throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*) as total FROM ")
           .append(TABLE_NAME)
           .append(" WHERE 1=1");
        
        List<Object> params = new ArrayList<>();
        
        if (name != null && !name.trim().isEmpty()) {
            sql.append(" AND name LIKE ?");
            params.add("%" + name + "%");
        }
        
        if (categoryId != null) {
            sql.append(" AND category_id = ?");
            params.add(categoryId);
        }

        ResultSet rs = (ResultSet) JDBCUtils.execute(sql.toString(), params.toArray());
        return rs.next() ? rs.getInt("total") : 0;
    }

    // ResultSet 映射到 Recipe 对象
    private Recipe mapResultSetToRecipe(ResultSet rs) throws SQLException {
        Recipe recipe = Recipe.builder()
                .id(rs.getInt("id"))
                .name(rs.getString("name"))
                .description(rs.getString("description"))
                .categoryId(rs.getInt("category_id"))
                .imagePath(rs.getString("image_path"))
                .createTime(rs.getTimestamp("create_time").toLocalDateTime())
                .build();
        
        // 设置分类名称
        recipe.setCategoryName(rs.getString("category_name"));
        
        return recipe;
    }

    // 添加收藏相关方法
    public boolean isFavorite(Integer userId, Integer recipeId) throws SQLException {
        String sql = "SELECT COUNT(*) as count FROM favorite WHERE user_id = ? AND recipe_id = ?";
        ResultSet rs = (ResultSet) JDBCUtils.execute(sql, userId, recipeId);
        return rs.next() && rs.getInt("count") > 0;
    }

    public boolean addFavorite(Integer userId, Integer recipeId) throws SQLException {
        // 先检查是否已收藏
        if (isFavorite(userId, recipeId)) {
            return false;
        }
        
        String sql = "INSERT INTO favorite (user_id, recipe_id) VALUES (?, ?)";
        int affected = (int) JDBCUtils.execute(sql, userId, recipeId);
        return affected > 0;
    }

    // 获取用户收藏的菜谱
    public List<Recipe> getFavoriteRecipes(Integer userId, Integer pageNum, Integer pageSize) 
            throws SQLException {
        String sql = "SELECT r.*, c.name as category_name FROM recipe r " +
                    "LEFT JOIN category c ON r.category_id = c.id " +
                    "INNER JOIN favorite f ON r.id = f.recipe_id " +
                    "WHERE f.user_id = ? LIMIT ?, ?";
        
        ResultSet rs = (ResultSet) JDBCUtils.execute(sql, 
                userId,
                (pageNum - 1) * pageSize,
                pageSize);

        List<Recipe> recipes = new ArrayList<>();
        while (rs.next()) {
            recipes.add(mapResultSetToRecipe(rs));
        }
        return recipes;
    }

    // 获取用户收藏总数
    public int getFavoriteTotalCount(Integer userId) throws SQLException {
        String sql = "SELECT COUNT(*) as total FROM favorite WHERE user_id = ?";
        ResultSet rs = (ResultSet) JDBCUtils.execute(sql, userId);
        return rs.next() ? rs.getInt("total") : 0;
    }

    // 在RecipeDao中添加取消收藏方法
    public boolean removeFavorite(Integer userId, Integer recipeId) throws SQLException {
        String sql = "DELETE FROM favorite WHERE user_id = ? AND recipe_id = ?";
        int affected = (int) JDBCUtils.execute(sql, userId, recipeId);
        return affected > 0;
    }
} 